#coding: utf-8

import datetime

import web
import skate
from libs import *
from config import app

LIMIT = 99999999 # select all from sql, XXX danger!!!

def get_count(tname, where=[]):
    sql = "SELECT COUNT(*) FROM %s" % tname
    if where:
        sql = "%s WHERE" % sql
        for i in where:
            sql = "%s %s=%s AND" % (sql, i['k'], web.db.sqlquote(i['v']))
        sql = sql[:-4]

    return skate.db.query(sql)[0]['COUNT(*)']

def new_user(email, password, nickname, 
        regip, regdate, lastip, lastdate):
    seqid = skate.db.insert(
            'user',
            email = email,
            password = password,
            nickname = nickname,
            location = '',
            regip = regip,
            regdate = regdate,
            lastip = regip,
            lastdate = regdate,
            super = 0,
            dele = 0,
            credit = 0
            )

    return seqid

def update_user(uid, nickname, location, age, gender):
    seqid = skate.db.update(
            'user',
            nickname = nickname, 
            location = location,
            age = age,
            gender = gender,
            where="id=%d" % uid
            )

    return seqid

def new_avatar(uid, webpath, picname):
    pid = new_pic(uid, -1, picname, 0, webpath)

    seqid = skate.db.update(
            'user',
            avatar = pid,
            where = 'id=%d' % uid
            )

    return pid

def new_tiny_avatar(uid, webpath, picname):
    pid = new_pic(uid, -1, picname, 0, webpath)

    seqid = skate.db.update(
            'user',
            tiny_avatar = pid,
            where = 'id=%d' % uid
            )

    return pid

def new_pic(uid, alid, filename, size, webpath):
    seqid = skate.db.insert(
            'picture',
            uid = uid,
            albumid = alid,
            filename = filename,
            size = size,
            webpath = webpath
            )

    return seqid

def get_pic_path(pid):
    pic = skate.db.select(
            'picture',
            where="id = %d" % pid
            )
    if pic:
        return pic[0]['webpath']
    else:
        return None


def update_passwd(uid, passwd):
    seqid = skate.db.update(
            'user',
            password = passwd,
            where="id=%d" % uid
            )

    return seqid

def get_user(uid):
    user = skate.db.select('user', where="id=%d" % uid)[0]
    user['avatar'] = get_pic_path(user['avatar'])
    user['tiny_avatar'] = get_pic_path(user['tiny_avatar'])
    return user

def get_user_baseinfo(uid):
    user = skate.db.select('user', where="id=%d" % uid)[0]
    tiny_avatar = get_pic_path(user['tiny_avatar'])

    return (user['nickname'], tiny_avatar)

def get_avatar(uid):
    user = skate.db.select('user', where="id=%d" % uid)[0]
    avatar = get_pic_path(user['avatar'])
    return avatar 

def get_actsubject(actid):
    return skate.db.select('activity', where="id=%d" % actid)[0]['subject']

def update_user_lastactive(lastdate, lastip, id):
    seqid = skate.db.update(
            'user',
            lastdate = lastdate,
            lastip = lastip,
            where = "id=%d" % id
            )
    return seqid


def new_act(uid, subject, type, basecredit, gathertime, startime,
        gatherlocation, coordx, coordy, accuracy, distance, route, descr, ip):
    seqid = skate.db.insert(
            'activity',
            uid = uid,
            subject = subject,
            type = type,
            basecredit = basecredit,
            gathertime = gathertime,
            startime = startime,
            gatherlocation = gatherlocation,
            coordx = coordx,
            coordy = coordy,
            accuracy = accuracy,
            distance = distance,
            route = route,
            descr = descr, 
            ip = ip, 
            wish = 0,
            do = 0,
            )
    return seqid


def update_act(aid, uid, subject, type, basecredit, gathertime, startime,
        gatherlocation, coordx, coordy, accuracy, distance, route, descr, ip):
    seqid = skate.db.update(
            'activity',
            where="id=%d and uid=%d" % (aid, uid),
            subject = subject,
            type = type,
            basecredit = basecredit,
            gathertime = gathertime,
            startime = startime,
            gatherlocation = gatherlocation,
            coordx = coordx,
            coordy = coordy,
            accuracy = accuracy,
            distance = distance,
            route = route,
            descr = descr, 
            ip = ip, 
            )
    return seqid

def get_act(aid):
    """
    Get one activity
    """
    return skate.db.select('activity', where="id=%d" % aid)[0]


def get_update_acts(offset=0, limit=0):
    return skate.db.select(
            'activity',
            order="atime DESC",
            limit=limit,
            offset=offset,
            )

def get_limit_update_acts(limit=0):
    return skate.db.select(
            'activity',
            order="atime DESC",
            limit=limit,
            )

def get_own_acts(uid, offset=0, limit=0):
    return skate.db.select(
            'activity',
            offset=offset,
            limit=limit,
            where="uid=%d" % uid,
            order="atime DESC",
            )


def get_user_acts(uid, offset=0):
    acts = []

    relas = skate.db.select(
            'relation',
            what="aid",
            offset=offset,
            limit=LIMIT,
            where="uid=%d" % uid,
            order="atime DESC",
            )

    for i in relas:
        act = skate.db.select(
                'activity',
                where="id=%d" % i['aid']
                )
        acts += act

    return acts

def get_user_rel_acts(uid, relation, offset=0, limit=0):
    acts = []

    relas = skate.db.select(
            'relation',
            what="aid",
            offset=offset,
            limit=limit,
            where="uid=%d and relation=%d" % (
                uid, relation
                ),
            order="atime DESC",
            )

    for i in relas:
        act = skate.db.select(
                'activity',
                where="id=%d" % i['aid']
                )
        acts += act

    return acts

def get_user_rel_acts_size(uid, relation):
    i = {'k': 'uid', 'v': uid}
    j = {'k': 'relation', 'v': relation}

    return get_count('relation', [i, j])

def get_user_wish_acts(uid,  offset=0, limit=0):
    return get_user_rel_acts(uid, 0, offset, limit)

def get_user_wish_acts_size(uid):
    return get_user_rel_acts_size(uid, 0)

def get_user_do_acts(uid, offset=0, limit=0):
    return get_user_rel_acts(uid, 1, offset, limit)

def get_user_do_acts_size(uid):
    return get_user_rel_acts_size(uid, 1)


def new_news(type, uid=-1, aid=-1, rid=-1, tid=-1, pid=-1, ava=-1,
        sid=-1, pcid=-1):
    """uid: user id
    aid: activity id
    rid: relation id
    tid: thread id
    pid: post id
    ava: avatar id
    sid: user say id
    pcid: picture id
    """
    seqid = skate.db.insert(
            'news',
            type=type,
            uid = uid, aid = aid, rid = rid, tid = tid,
            pid = pid, ava = ava, sid = sid, pcid = pcid
            )
    return seqid

def handle_relation(uid, aid, relation):

    if relation < 2: #wish or do
        rela = skate.db.select(
                'relation',
                where="uid=%d and aid=%d" % (
                    uid, aid)
                )
        if len(rela) > 0:
            # user already wished, change status to done
            seqid = skate.db.update(
                    'relation',
                    relation = relation,
                    where="uid=%d and aid=%d" % (
                        uid, aid)
                    )
        else:
            # new user, add new status
            seqid = skate.db.insert(
                    'relation',
                    uid = uid,
                    aid = aid,
                    relation = relation,
                    )

            seqid = new_news(type=NEWS['relation'], uid=uid, rid=seqid)

    else: #cancel
        rid = skate.db.select(
                'relation',
                where="uid=%d and aid=%d" % (uid, aid)
                )[0]['id']
        reqid = skate.db.delete(
                'relation',
                where="uid=%d and aid=%d" % (uid, aid)
                )

        reqid = skate.db.delete(
                'news',
                where="type=%d and uid=%d and rid=%d" % (
                    NEWS['relation'], uid, rid
                    )
                )

    wishes = len(skate.db.select(
        'relation',
        where="aid=%d and relation=0" % (
            aid))
        )

    does = len(skate.db.select(
        'relation',
        where="aid=%d and relation=1" % (
            aid))
        )

    seqid = skate.db.update(
            'activity',
            wish=wishes,
            do=does,
            where="id=%d" % aid
            )

    return seqid

def get_rel_act_wish(aid):
    return skate.db.select(
            'relation',
            where="aid=%d and relation=0" % aid
            )
    
def get_rel_act_do(aid):
    return skate.db.select(
            'relation',
            where="aid=%d and relation=1" % aid
            )

def get_act_rela(uid, aid):
    wish, do = False, False
    rela = skate.db.select(
            'relation',
            where="uid=%d and aid=%d" % (uid, aid)
            )

    if rela:
        for i in rela:
            if i['relation'] == 0:
                wish = True
            if i['relation'] == 1:
                do = True

    return (wish, do)


def new_thread(uid, aid, subject, descr, ip):
    seqid = skate.db.insert(
            'activity_threads',
            aid = aid,
            uid = uid,
            up = 0,
            subject = subject,
            descr = descr,
            ip = ip,
            lastreply = datetime.datetime.now(),
            reply = 0
            )

    return seqid

def get_threads(uid=-1, aid=-1):
    threads = []
    if uid > 0:
        threads = skate.db.select(
                'activity_threads',
                where='uid=%d and dele=0' % uid,
                order="lastreply DESC, up",
                )
    if aid > 0:
        threads = skate.db.select(
                'activity_threads',
                where='aid=%d and dele=0' % aid,
                order="up DESC, lastreply DESC",
                )
    tmp = []
    for i in threads:
        (nickname, tiny_avatar) = get_user_baseinfo(i['uid'])
        i['owner'] = nickname
        tmp.append(i)
    
    threads = __readable_datetime(tmp, ['atime'])

    return threads

def get_thread_detail(tid):
    thread = skate.db.select(
            'activity_threads',
            where="id=%d" % tid)[0]
    thread['activity'] = get_actsubject(thread['aid'])
    thread['owner'], thread['owner_avatar'] = get_user_baseinfo(thread['uid'])

    thread = __readable_datetime([thread], ['atime'])[0]

    return thread

def thread_up(tid):
    seqid = skate.db.update(
            'activity_threads',
            up = 1,
            where="id=%d" % tid
            )

    return seqid

def thread_down(tid):
    seqid = skate.db.update(
            'activity_threads',
            up = 0,
            where="id=%d" % tid
            )

    return seqid

def thread_del(tid):
    seqid = skate.db.update(
            'activity_threads',
            dele = 1,
            where="id=%d" % tid
            )

    skate.db.delete(
            'news',
            where="tid=%d" % tid
            )

    return seqid

def get_posts(tid):
    posts = skate.db.select(
            'activity_posts',
            where="atid=%d" % tid
            )

    tmp = []
    for i in posts:
        i['owner'], i['owner_avatar'] = get_user_baseinfo(i['uid'])
        tmp.append(i)

    posts = __readable_datetime(tmp, ['atime'])

    return posts

def get_post(pid):
    post = skate.db.select (
            'activity_post',
            where="id=%d" % pid
            )[0]

    return post

def new_post(uid, tid, descr, ip):
    seqid = skate.db.insert(
            'activity_posts',
            atid = tid,
            uid = uid,
            descr = descr,
            ip = ip)

    reply = skate.db.select(
            'activity_threads',
            where="id=%d" % tid)[0]['reply']

    skate.db.update(
            'activity_threads',
            reply = reply + 1,
            lastreply = datetime.datetime.now(),
            where="id=%d" % tid)

    return seqid

def new_feedback(username, descr, ip):
    seqid = skate.db.insert(
            'feedback',
            username = username,
            descr = descr,
            ip = ip,
            )

    return seqid


def get_feedbacks(limit=0, offset=0):
    feedbacks = skate.db.select(
            'feedback',
            order="atime DESC",
            limit=limit,
            offset=offset,
            where="dele=0",
            )

    return feedbacks

def get_feedback(fdid):
    feedback = skate.db.select(
            'feedback',
            where="id=%d and dele=0" % fdid,
            )[0]

    return feedback

def update_feedback_reply(fdid, reply):
    seqid = skate.db.update(
            'feedback',
            where="id=%d" % fdid,
            reply = reply
            )

    return seqid

def __readable_datetime(unreadable, tablename):

    assert(type(tablename) == list)

    tmp = []
    for i in unreadable:
        for j in tablename:
            i[j] = i[j].strftime(
                    "%m月%d日 %H:%M")
        tmp.append(i)

    return tmp
    


